package org.edu.eece262.taskshare;

import java.util.LinkedList;

import org.edu.eece262.taskshare.Messages.Task;

import com.google.gson.Gson;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class TaskDBAdapter {
	
	// Let's be a Singleton
	private static TaskDBAdapter instance = null;

	// Member Variables
	private static String DB_NAME = "TaskShare.db";
	private static final String DB_TABLE = "LocalTasks";

	private static final int DB_VERSION = 20;

	public static final String DB_KEY_ID = "_id";

	// Public Key (Column) Names for convenience
	public static final String KEY_GLOBAL_ID = "GlobalID";
	public static final String KEY_NAME = "Name";
	public static final String KEY_OWNER = "Owner";
	public static final String KEY_ASSIGNED_LIST = "AssignedList";
	public static final String KEY_NOTES = "Notes";
	public static final String KEY_LOCATION = "Location";
	public static final String KEY_PARENT_LOCAL = "ParentLocal";
	public static final String KEY_PARENT_GLOBAL = "ParentGlobal";
	public static final String KEY_DUE_DATE = "DueDate";
	public static final String KEY_DUE_TIME = "DueTime";
	public static final String KEY_PERCENT_COMPLETE = "PercentComplete";
	public static final String KEY_DONE = "Done";
	public static final String KEY_PRIORITY = "Priority";
	public static final String KEY_TIMESTAMP = "Timestamp";

	// Public Column numbers for convenience
	public static final int GLOBAL_ID_COLUMN = 1;
	public static final int NAME_COLUMN = 2;
	public static final int OWNER_COLUMN = 3;
	public static final int ASSIGNED_LIST_COLUMN = 4;
	public static final int NOTES_COLUMN = 5;
	public static final int LOCATION_COLUMN = 6;
	public static final int PARENT_LOCAL_COLUMN = 7;
	public static final int PARENT_GLOBAL_COLUMN = 8;
	public static final int DUE_DATE_COLUMN = 9;
	public static final int DUE_TIME_COLUMN = 10;
	public static final int PERCENT_COMPLETE_COLUMN = 11;
	public static final int DONE_COLUMN = 12;
	public static final int PRIORITY_COLUMN = 13;
	public static final int TIMESTAMP_COLUMN = 14;

	private static final String DB_CREATE = "create table " + DB_TABLE + " ("
			+ DB_KEY_ID + " integer primary key autoincrement, "
			+ KEY_GLOBAL_ID + " long, " + KEY_NAME + " text, " + KEY_OWNER + " text, " + KEY_ASSIGNED_LIST + " text, " + KEY_NOTES
			+ " text, " + KEY_LOCATION + " text, " + KEY_PARENT_LOCAL
			+ " int, " + KEY_PARENT_GLOBAL + " int, " + KEY_DUE_DATE + " int, "
			+ KEY_DUE_TIME + " int, " + KEY_PERCENT_COMPLETE + " int, " + KEY_DONE + " int, "
			+ KEY_PRIORITY + " int, " + KEY_TIMESTAMP + " long);";

	private SQLiteDatabase db;

	private final Context context;
	private DBHelper dbHelper;
	private static Gson gson;
	
	public static final String PREFS_NAME = "TASKSHARELOGIN";

	public static TaskDBAdapter getTSDBAdapter(Context _context) {
		SharedPreferences settings = _context.getSharedPreferences(PREFS_NAME, 0);
		if(instance == null || !(DB_NAME.equals(settings.getString("user", "") + "TaskShare.db"))) {
			DB_NAME = settings.getString("user", "") + "TaskShare.db";
			instance = new TaskDBAdapter(_context);
			gson = new Gson();
			return instance;
		} else {
			return instance;
		}
	}
	
	private TaskDBAdapter(Context _context) {
		context = _context;
		dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
	}

	public TaskDBAdapter open() throws SQLException {
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		db.close();
	}

	public long insertEntry(String name, String owner, String notes, String location,
			int parentlocal, long parentglobal, int duedate, int duetime,
			int percentcomplete, int priority, Context context) {
		
		LinkedList<String> assignedList = new LinkedList<String>();
		assignedList.add(owner);
		
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_GLOBAL_ID, 0);
		contentValues.put(KEY_NAME, name);
		contentValues.put(KEY_OWNER, owner);
		contentValues.put(KEY_ASSIGNED_LIST, gson.toJson(assignedList));
		contentValues.put(KEY_NOTES, notes);
		contentValues.put(KEY_LOCATION, location);
		contentValues.put(KEY_PARENT_LOCAL, parentlocal);
		contentValues.put(KEY_PARENT_GLOBAL, parentglobal);
		contentValues.put(KEY_DUE_DATE, duedate);
		contentValues.put(KEY_DUE_TIME, duetime);
		contentValues.put(KEY_PERCENT_COMPLETE, percentcomplete);
		contentValues.put(KEY_PRIORITY, priority);
		contentValues.put(KEY_DONE, 0);
		contentValues.put(KEY_TIMESTAMP, 0);
		
		int key = (int) db.insert(DB_TABLE, null, contentValues);
		
		SharedPreferences settings = context.getSharedPreferences(PREFS_NAME, 0);
		HTTPHelp.getActivityHTTPHelp().createTask(new Task()
												.setGlobal_id(0)
												.setAssignedList(assignedList)
												.setName(name)
												.setNotes(notes)
												.setLocation(location)
												.setGlobal_parent(parentglobal)
												.setDue_date(duedate)
												.setDue_time(duetime)
												.setPercent_complete(percentcomplete)
												.setPriority(priority)
												.setDone(0)
												.setLocal_id(key), 
										settings.getString("user", ""), context);
		
		return key;
	}
	
	public long insertEntryFromGlobal(long localID, long globalID, String name, String owner,
			LinkedList<String> assignedList, String notes, String location, long parentglobal,
			int duedate, int duetime, int percentcomplete, int priority, long timestamp) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_GLOBAL_ID, globalID);
		contentValues.put(KEY_ASSIGNED_LIST, gson.toJson(assignedList));
		contentValues.put(KEY_NAME, name);
		contentValues.put(KEY_NOTES, notes);
		contentValues.put(KEY_LOCATION, location);
		contentValues.put(KEY_PARENT_GLOBAL, parentglobal);
 		Cursor c = getTaskWithGlobalID(parentglobal);
		c.moveToFirst();
		if(c.getCount() <= 0)
			{ contentValues.put(KEY_PARENT_LOCAL, 0); }
		else
			{ contentValues.put(KEY_PARENT_LOCAL, c.getInt(0)); }
		contentValues.put(KEY_DUE_DATE, duedate);
		contentValues.put(KEY_DUE_TIME, duetime);
		contentValues.put(KEY_PERCENT_COMPLETE, percentcomplete);
		contentValues.put(KEY_PRIORITY, priority);
		contentValues.put(KEY_TIMESTAMP, timestamp);
		
		return db.insert(DB_TABLE, null, contentValues);
	}

	public boolean removeEntry(int _rowIndex, Context context) {
		Cursor c = getTaskWithLocalID(_rowIndex);
		c.moveToFirst();
		if(c.getCount() > 0) {
			SharedPreferences settings = context.getSharedPreferences(PREFS_NAME, 0);
			HTTPHelp.getActivityHTTPHelp().deleteTask(new Task()
			.setGlobal_id(c.getLong(GLOBAL_ID_COLUMN))
			.setName(c.getString(NAME_COLUMN))
			.setNotes(c.getString(NOTES_COLUMN))
			.setLocation(c.getString(LOCATION_COLUMN))
			.setGlobal_parent(c.getLong(PARENT_GLOBAL_COLUMN))
			.setDue_date(c.getInt(DUE_DATE_COLUMN))
			.setDue_time(c.getInt(DUE_TIME_COLUMN))
			.setPercent_complete(c.getInt(PERCENT_COMPLETE_COLUMN))
			.setPriority(c.getInt(PRIORITY_COLUMN))
			.setDone(c.getInt(DONE_COLUMN))
			.setLocal_id(c.getInt(0)), 
			settings.getString("user", ""), context);
		}
		return db.delete(DB_TABLE, DB_KEY_ID + "=" + _rowIndex, null) > 0;
	}

	public Cursor getAllEntries() {
		return db.query(DB_TABLE, null, null, null, null, null, null);
	}
	
	public Cursor getTopLevelEntries() {
		return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=0", null, null, null, null);
	}
	
	public Cursor getChildrenOf(int local_id) {
		return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=" + local_id, null, null, null, null);
	}
	
	public Cursor getTaskWithLocalID(int local_id) {
		return db.query(DB_TABLE, null, DB_KEY_ID + "=" + local_id, null, null, null, null);
	}
	
	public Cursor getTaskWithGlobalID(long global_id) {
		return db.query(DB_TABLE, null, KEY_GLOBAL_ID + "=" + global_id, null, null, null, null);
	}

	public Cursor getTopLevelEntriesSorted(String sort) {
		if (sort.equals("priority")) {
			return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=0", null, null, null,
					KEY_PRIORITY + " DESC");
		}
		if (sort.equals("due"))
		{
			return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=0", null, null, null,
					KEY_DUE_DATE + " ASC, " + KEY_DUE_TIME + " ASC");
		}
		return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=0", null, null, null, null);
	}

	public int updateEntry(long localID, long globalID, String name,
			String notes, String location, int parentlocal, long parentglobal,
			int duedate, int duetime, int percentcomplete, int priority) {
		String where = DB_KEY_ID + "=" + localID;
		ContentValues contentValues = new ContentValues();
		int done = percentcomplete == 100 ? 1 : 0;
		contentValues.put(KEY_GLOBAL_ID, globalID);
		contentValues.put(KEY_NAME, name);
		contentValues.put(KEY_NOTES, notes);
		contentValues.put(KEY_LOCATION, location);
		contentValues.put(KEY_PARENT_LOCAL, parentlocal);
		contentValues.put(KEY_PARENT_GLOBAL, parentglobal);
		contentValues.put(KEY_DUE_DATE, duedate);
		contentValues.put(KEY_DUE_TIME, duetime);
		contentValues.put(KEY_PERCENT_COMPLETE, percentcomplete);
		contentValues.put(KEY_PRIORITY, priority);
		contentValues.put(KEY_DONE, done);
		
		int key = db.update(DB_TABLE, contentValues, where, null);
		
		SharedPreferences settings = context.getSharedPreferences(PREFS_NAME, 0);
		HTTPHelp.getActivityHTTPHelp().updateTask(new Task()
												.setGlobal_id(globalID)
												.setName(name)
												.setNotes(notes)
												.setLocation(location)
												.setGlobal_parent(parentglobal)
												.setDue_date(duedate)
												.setDue_time(duetime)
												.setPercent_complete(percentcomplete)
												.setPriority(priority)
												.setDone(done)
												.setLocal_id(key), 
										settings.getString("user", ""), context);
		return key;
	}
	
	public int updateEntryFromGlobal(long localID, long globalID, String name, String owner,
			LinkedList<String> assignedList, String notes, String location, long parentglobal,
			int duedate, int duetime, int percentcomplete, int priority, long timestamp) {
		String where = KEY_GLOBAL_ID + "=" + globalID;
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_GLOBAL_ID, globalID);
		contentValues.put(KEY_ASSIGNED_LIST, gson.toJson(assignedList));
		contentValues.put(KEY_NAME, name);
		contentValues.put(KEY_NOTES, notes);
		contentValues.put(KEY_LOCATION, location);
		contentValues.put(KEY_PARENT_GLOBAL, parentglobal);
		contentValues.put(KEY_DUE_DATE, duedate);
		contentValues.put(KEY_DUE_TIME, duetime);
		contentValues.put(KEY_PERCENT_COMPLETE, percentcomplete);
		contentValues.put(KEY_PRIORITY, priority);
		contentValues.put(KEY_TIMESTAMP, timestamp);
		Cursor children = getChildrenOf((int)localID);
		while(!children.isAfterLast()) {
			changeParent(children.getInt(0), (int) localID, globalID);
		}
		
		return db.update(DB_TABLE, contentValues, where, null);
	}
	
	public int updateNewEntryFromGlobal(long localID, long globalID, String name, String owner,
			LinkedList<String> assignedList, String notes, String location, long parentglobal,
			int duedate, int duetime, int percentcomplete, int priority, long timestamp) {
		String where = DB_KEY_ID + "=" + localID;
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_GLOBAL_ID, globalID);
		contentValues.put(KEY_ASSIGNED_LIST, gson.toJson(assignedList));
		contentValues.put(KEY_NAME, name);
		contentValues.put(KEY_NOTES, notes);
		contentValues.put(KEY_LOCATION, location);
		contentValues.put(KEY_PARENT_GLOBAL, parentglobal);
		contentValues.put(KEY_DUE_DATE, duedate);
		contentValues.put(KEY_DUE_TIME, duetime);
		contentValues.put(KEY_PERCENT_COMPLETE, percentcomplete);
		contentValues.put(KEY_PRIORITY, priority);
		contentValues.put(KEY_TIMESTAMP, timestamp);
		Cursor children = getChildrenOf((int)localID);
		while(!children.isAfterLast()) {
			changeParent(children.getInt(0), (int) localID, globalID);
		}
		
		return db.update(DB_TABLE, contentValues, where, null);
	}
	
	public int changeParent(long localID, int parentLocal, long parentGlobal)
	{
		String where = DB_KEY_ID + "=" + localID;
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_PARENT_LOCAL, parentLocal);
		contentValues.put(KEY_PARENT_GLOBAL, parentGlobal);
		return db.update(DB_TABLE, contentValues, where, null);
	}
	
	public int markDone(long localID, int done) {
		String where = DB_KEY_ID + "=" + localID;
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_DONE, done);
		return db.update(DB_TABLE, contentValues, where, null);
	}

	private static class DBHelper extends SQLiteOpenHelper {
		public DBHelper(Context context, String name, CursorFactory factory,
				int version) {
			super(context, name, factory, version);
		}

		// Called when no database exists in
		// disk and the helper class needs
		// to create a new one.
		@Override
		public void onCreate(SQLiteDatabase _db) {
			_db.execSQL(DB_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion,
				int _newVersion) {
			// Log the version upgrade.
			Log.w("TaskDBAdapter", "Upgrading from version " + _oldVersion
					+ " to " + _newVersion
					+ ", which will destroy all old data");

			_db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);

			onCreate(_db);
		}
	}

	public boolean removeEntryByGlobal(long globalId) {
		return db.delete(DB_TABLE, KEY_GLOBAL_ID + "=" + globalId, null) > 0;
		
	}
}
